﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Orchard.Test.Library
{
    public class CopyAndCoverTables
    {
        /// <summary>
        /// enum the table type:
        /// Old tables are create by setup module
        /// CopyedTables are create by the first automation test cases
        /// </summary>
        public enum TableType
        {
            AllTables = 0,
            OldTables = 1,
            CopyedTables = 2
        };

        //Old table name +strBackTable=copyed table name
        public string strBackTable = "_Copy";

        /// <summary>
        /// Get the tables name of orchard
        /// </summary>
        /// <param name="intFlg">0:All the tables;1:The old tables create by Setup moudle;2:Copyed tables;</param>
        /// <returns></returns>
        public DataTable GetTables(TableType tableType)
        {
            //Sql statement
            StringBuilder strSql = new StringBuilder();

            strSql.AppendLine("use Orchard");
            strSql.AppendLine("SELECT name FROM sysobjects where xtype='u'");

            //Get the tables name create by the setup module
            if (tableType == TableType.OldTables)
            {
                strSql.AppendLine("and name not like '%" + strBackTable + "'");
            }
                //get the tables name create by automation test cases
            else if (tableType == TableType.CopyedTables)
            {
                strSql.AppendLine("and name  like '%"+strBackTable+"'");
            }
            return  DBAcess.SelectFromDB(strSql.ToString());
             
        }

        /// <summary>
        /// Copy the tables with the old +"_Copy" name 
        /// </summary>
        /// <returns>0:failed to copy 1: Cope the table</returns>
        public int CopyTable()
        {
            DataTable dt = new DataTable();
            StringBuilder strSql = new StringBuilder();

            //result flag
            int intFlg = 0;

            //get the old table name
            dt = GetTables(TableType.OldTables);

            //Copy the tables(include the data, not only table structure)
            for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
            {
                strSql.AppendLine("select  * into  " + dt.Rows[intRowCount][0].ToString() + strBackTable + "  from " + dt.Rows[intRowCount][0].ToString());
            }

            if (strSql.Length > 0)
            {
                intFlg=DBAcess.ExecuteSQLWithRollback(strSql.ToString());
            }

            return intFlg;
        }

        /// <summary>
        /// Copy the tables with the same name 
        /// </summary>
        /// <returns>0:failed to copy 1: Cope the table</returns>
        public int RecoverTable()
        {
            DataTable dt = new DataTable();
            StringBuilder strSql = new StringBuilder();

            //result flg
            int intFlg = 0;

            //Get the copyed tables name
            dt = GetTables(TableType.CopyedTables);

            //Cover the tables create by setup module
            for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
            {
                strSql.AppendLine("select  * into  " + dt.Rows[intRowCount][0].ToString().Substring(0,dt.Rows[intRowCount][0].ToString().Length-5) + "  from " + dt.Rows[intRowCount][0].ToString());
            }

            if (strSql.Length > 0)
            {
                intFlg = DBAcess.ExecuteSQLWithRollback(strSql.ToString());
            }

            return intFlg;
        }

        /// <summary>
        /// Delete the old tables create by setup moudle
        /// </summary>
        /// <returns>0: failed to delete 1: deleted</returns>
        public int DeleteOldTables()
        {
            DataTable dt = new DataTable();
            StringBuilder strSql = new StringBuilder();

            //result flg
            int intFlg = 0;

            //get the tables create by setup module
            dt = GetTables(TableType.OldTables);

            //delete the tables
            for (int intRowCount = 0; intRowCount < dt.Rows.Count; intRowCount++)
            {
                strSql.AppendLine("drop table  " + dt.Rows[intRowCount][0].ToString() );
            }

            if (strSql.Length > 0)
            {
                intFlg = DBAcess.ExecuteSQLWithRollback(strSql.ToString());
            }

            return intFlg;
        }
        
    }
}
